﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using InvestmentIntelligence.Data.Repository.EFImplementation;
using InvestmentIntelligence.DB;
using InvestmentIntelligence.DbModel.CustomModels;
using InvestmentIntelligence.DbModel.Models;

namespace InvestmentIntelligence.Data.Repository.SqlImplementation
{
    public class InvestmentRiskSerieRepository : IIPRepository<InvestmentRiskSerie>, IInvestmentRiskSerieRepository
    {
        private const int MergeTimeout = 300;

        public void Merge(IEnumerable<InvestmentRiskSerie> items, IEnumerable<int> investments)
        {
            using (var dc = new DataConnection())
            {
                dc.Begin();

                var tempInvestmentsTable = CreateTempTable(dc, "InvestmentId");
                var tempSeriesTable = CreateTempTable(dc, "*");
                dc.BulkCopy(tempInvestmentsTable, investments.Select(i => new {InvestmentId = i}));
                dc.BulkCopy(tempSeriesTable, items);
                ExecuteMerge(dc, tempSeriesTable, tempInvestmentsTable);

                dc.Commit();
        }
        }

        private string CreateTempTable(DataConnection dc, string columns)
        {
            var tempTableName = "##InvestmentRiskSerie" + Guid.NewGuid().ToString().Replace("-", "");
            dc.Execute(string.Format("SELECT {0} INTO {1} FROM [Analytics].[InvestmentRiskSerie] WHERE 1=2", columns, tempTableName), CommandType.Text);
            return tempTableName;
        }

        private void ExecuteMerge(DataConnection dc, string tempSeriesTable, string tempInvestmentsTable)
        {
            const string mergePattern = @"
WITH targetSeries AS (
    SELECT 
        * 
    FROM
        [Analytics].[InvestmentRiskSerie]
    WHERE
        InvestmentId IN (SELECT InvestmentId FROM {0})
)
MERGE
	targetSeries s
USING
	{1} st
ON
	s.[InvestmentId] = st.[InvestmentId] AND s.[Date] = st.[Date]
WHEN MATCHED THEN 
	UPDATE SET s.[Risk] = st.[Risk]
WHEN NOT MATCHED THEN 
	INSERT ([InvestmentId],[Date],[Risk])
    VALUES ([InvestmentId],[Date],[Risk])
WHEN NOT MATCHED BY SOURCE THEN
	DELETE
OPTION (MAXDOP 1)
;
";
            var sqlCode = string.Format(mergePattern, tempInvestmentsTable, tempSeriesTable);
            dc.Timeout = MergeTimeout;
            dc.Execute(sqlCode, CommandType.Text);
        }

        private static readonly List<InvestmentRiskSerie> EmptyRisks = new List<InvestmentRiskSerie>(); 

        public List<InvestmentRiskSerie> GetInvestmentsRiskSeries(IEnumerable<int> investmentIds)
        {
            if (!investmentIds.Any()) return EmptyRisks;

            var comm = string.Format(@"
SELECT serie.*
FROM [Analytics].[InvestmentRiskSerie] serie
WHERE InvestmentId IN ({0})
ORDER BY [Date]", string.Join(", ", investmentIds));

            using (var dc = new DataConnection())
            {
                return dc.Query<InvestmentRiskSerie>(comm, CommandType.Text).ToList();
            }
        }
    }
}
